﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using StuMana.Services;
using MySql.Data.MySqlClient;


namespace StuMana.UI
{
    public partial class Schedule : Office2007Form
    {
        public Schedule()
        {
            InitializeComponent();
        }

        public int level = 0;
        DataTable dataSang; 
        DataTable dataChieu;
         public  class time
        {
            public string _start;
            public string _end;

            public time()
            {
                _start = "NONE";
                _end = "NONE";
            }

            public time(string start, string end)
            {
                _start = start;
                _end = end;
            }
                 
        }

        public void LoadDanhSachClass()
        {           
            cmbClass.Items.Add("10A1");
            cmbClass.Items.Add("10A2");
            cmbClass.Items.Add("10A3");
            cmbClass.Items.Add("10A4");
            cmbClass.Items.Add("10A5");
            cmbClass.Items.Add("10A6");
            cmbClass.Items.Add("10A7");
            cmbClass.Items.Add("10A8");
            cmbClass.Items.Add("10A9");

            cmbClass.Items.Add("11A1");
            cmbClass.Items.Add("11A2");
            cmbClass.Items.Add("11A3");
            cmbClass.Items.Add("11A4");
            cmbClass.Items.Add("11A5");
            cmbClass.Items.Add("11A6");
            cmbClass.Items.Add("11A7");
            cmbClass.Items.Add("11A8");
            cmbClass.Items.Add("11A9");

            cmbClass.Items.Add("12A1");
            cmbClass.Items.Add("12A2");
            cmbClass.Items.Add("12A3");
            cmbClass.Items.Add("12A4");
            cmbClass.Items.Add("12A5");
            cmbClass.Items.Add("12A6");
            cmbClass.Items.Add("12A7");
            cmbClass.Items.Add("12A8");
            cmbClass.Items.Add("12A9");

            cmbClass.SelectedIndex = 0;
        }
    
        //Get data from datatable 
        public List<time> GetTimeData(DataTable data)
        {
            List<time> li = new List<time>();
            if (data.Rows.Count == 0)
                return li;
            for (int i = 0; i < data.Columns.Count; i++)
            {
                string value = data.Rows[0][i].ToString();
                string[] temp = value.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                time ti = new time (temp[0],temp[1]);
                li.Add(ti);
            }
            return li;
        }

        public void LoadTimeSang(DataTable data )
        {
            List<time> li = new List<time>();
            li = GetTimeData(data);
            dgvSang.AutoGenerateColumns = false;

            DataTable dt = new DataTable();
            dt.Columns.Add("Monday", typeof(String));
            dt.Columns.Add("Tuesday", typeof(String));
            dt.Columns.Add("Wednesday", typeof(String));
            dt.Columns.Add("Thursday", typeof(String));
            dt.Columns.Add("Friday", typeof(String));
            dt.Columns.Add("Saturday", typeof(String));

            if (data.Rows.Count > 0)
            {
                dt.Rows.Add(new object[] { li[0]._start, li[1]._start, li[2]._start, li[3]._start, li[4]._start, li[5]._start });
                dt.Rows.Add(new object[] { li[0]._end, li[1]._end, li[2]._end, li[3]._end, li[4]._end, li[5]._end });

            }

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Monday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listMonday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Monday.DataSource = listMonday;
            Monday.HeaderText = "Monday";
            Monday.DataPropertyName = "Monday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Tuesday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listTuesday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Tuesday.DataSource = listTuesday;
            Tuesday.HeaderText = "Tuesday";
            Tuesday.DataPropertyName = "Tuesday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Wednesday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listWednesday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Wednesday.DataSource = listWednesday;
            Wednesday.HeaderText = "Wednesday";
            Wednesday.DataPropertyName = "Wednesday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Thursday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listThursday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Thursday.DataSource = listThursday;
            Thursday.HeaderText = "Thursday";
            Thursday.DataPropertyName = "Thursday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Friday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listFriday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Friday.DataSource = listFriday;
            Friday.HeaderText = "Friday";
            Friday.DataPropertyName = "Friday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Saturday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listSaturday = new List<string>() { "NONE", "07:00:00", "07:45:00", "09:45:00", "10:30:00", "11:15:00" };
            Saturday.DataSource = listSaturday;
            Saturday.HeaderText = "Saturday";
            Saturday.DataPropertyName = "Saturday";
            dgvSang.DataSource = dt;
            if(dgvSang.Columns.Count!=6)
                dgvSang.Columns.AddRange(Monday, Tuesday, Wednesday, Thursday, Friday, Saturday);
        }

        public void LoadTimeChieu(DataTable data)
        {
            List<time> li = new List<time>();
            li = GetTimeData(data);
            dgvChieu.AutoGenerateColumns = false;

            DataTable dt = new DataTable();
            dt.Columns.Add("Monday", typeof(String));
            dt.Columns.Add("Tuesday", typeof(String));
            dt.Columns.Add("Wednesday", typeof(String));
            dt.Columns.Add("Thursday", typeof(String));
            dt.Columns.Add("Friday", typeof(String));
            dt.Columns.Add("Saturday", typeof(String));

            if (data.Rows.Count > 0)
            {
                dt.Rows.Add(new object[] { li[0]._start, li[1]._start, li[2]._start, li[3]._start, li[4]._start, li[5]._start });
                dt.Rows.Add(new object[] { li[0]._end, li[1]._end, li[2]._end, li[3]._end, li[4]._end, li[5]._end });

            }

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Monday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listMonday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Monday.DataSource = listMonday;
            Monday.HeaderText = "Monday";
            Monday.DataPropertyName = "Monday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Tuesday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listTuesday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Tuesday.DataSource = listTuesday;
            Tuesday.HeaderText = "Tuesday";
            Tuesday.DataPropertyName = "Tuesday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Wednesday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listWednesday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Wednesday.DataSource = listWednesday;
            Wednesday.HeaderText = "Wednesday";
            Wednesday.DataPropertyName = "Wednesday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Thursday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listThursday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Thursday.DataSource = listThursday;
            Thursday.HeaderText = "Thursday";
            Thursday.DataPropertyName = "Thursday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Friday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listFriday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Friday.DataSource = listFriday;
            Friday.HeaderText = "Friday";
            Friday.DataPropertyName = "Friday";

            DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn Saturday = new DevComponents.DotNetBar.Controls.DataGridViewComboBoxExColumn();
            var listSaturday = new List<string>() { "NONE", "13:00:00", "13:45:00", "14:45:00", "15:30:00", "16:45:00", "17:00:00" };
            Saturday.DataSource = listSaturday;
            Saturday.HeaderText = "Saturday";
            Saturday.DataPropertyName = "Saturday";
            dgvChieu.DataSource = dt;
            if(dgvChieu.Columns.Count!=6)
                dgvChieu.Columns.AddRange(Monday, Tuesday, Wednesday, Thursday, Friday, Saturday);
        }

        private void Schedule_Load(object sender, EventArgs e)
        {
            LoadDanhSachClass();
            LoadSchedule(cmbClass.Text, 1);
            LoadSchedule(cmbClass.Text, 2);
            LoadTimeSang(dataSang);
            LoadTimeChieu(dataChieu);
            if (level != 0)
            {
                btnSave.Enabled = false;
            }
            else
                btnSave.Enabled = true;         
        }

        public bool LoadSchedule(string className, int session)
        {
            ConnectionController control = new ConnectionController();
            control.connection.Open();
            try
            {
                MySql.Data.MySqlClient.MySqlCommand cmd = control.connection.CreateCommand();
                cmd.CommandText = " SELECT T2 as Monday, T3 as Tuesday, T4 as Wednesday, T5 as Thursday, T6 as Friday, T7 as Saturday ";
                cmd.CommandText += " FROM Schedule WHERE Session = " + session.ToString() + " and Class = '" + className.ToString()+"'";
                MySql.Data.MySqlClient.MySqlDataAdapter adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                if (session == 1)
                {
                   // dgvSang.DataSource = ds.Tables[0].DefaultView;
                    //if (dgvSang.Rows.Count> 0)
                    //{
                    //    dgvSang.Rows.RemoveAt(1);
                    //    dgvSang.Rows.RemoveAt(0);
                    //}
                    dataSang = new DataTable();
                    dataSang = ds.Tables[0];
                }
                if (session == 2)
                {
                  //  dgvChieu.DataSource = ds.Tables[0].DefaultView;
                    //if (dgvChieu.Rows.Count > 0)
                    //{
                    //    dgvChieu.Rows.RemoveAt(1);
                    //    dgvChieu.Rows.RemoveAt(0);
                    //}
                    dataChieu = new DataTable();
                    dataChieu = ds.Tables[0];
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (control.connection.State == ConnectionState.Open)
                    control.connection.Clone();
            }
            return true;
        }

        private void cmbClass_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadSchedule(cmbClass.Text, 1);
            LoadSchedule(cmbClass.Text, 2);
            LoadTimeSang(dataSang);
            LoadTimeChieu(dataChieu);

            if (dgvSang.Columns.Count > 0)
            {
                int widthC = ( dgvSang.Size.Width - 50) / dgvSang.Columns.Count ;
                int i;
                for ( i = 0; i < dgvSang.Columns.Count -1 ; i++)
                {
                    dgvSang.Columns[i].Width = widthC ;
                }
                dgvSang.Columns[i].Width = widthC + 9;
            }

            if (dgvChieu.Columns.Count > 0)
            {
                int widthC = (dgvChieu.Size.Width - 50 )/ dgvChieu.Columns.Count ;
                int i;
                for ( i = 0; i < dgvChieu.Columns.Count -1 ; i++)
                {
                    dgvChieu.Columns[i].Width = widthC;
                }
                dgvChieu.Columns[i].Width = widthC + 9;
            }
        }

        public List<time> GetListUpdateSang()
        {
            List<time> li = new List<time>();
            for (int i = 0; i < dgvSang.Columns.Count; i++)
            {
                for (int j = 0; j < dgvSang.Rows.Count-1; j++)
                {
                    time ti = new time(dgvSang.Rows[j].Cells[i].Value.ToString(), dgvSang.Rows[j + 1].Cells[i].Value.ToString());
                    li.Add(ti);
                }
            }
            return li;
        }

        public List<time> GetListUpdateChieu()
        {
            List<time> li = new List<time>();
            for (int i = 0; i < dgvChieu.Columns.Count; i++)
            {
                for (int j = 0; j < dgvChieu.Rows.Count - 1; j++)
                {
                    time ti = new time(dgvChieu.Rows[j].Cells[i].Value.ToString(), dgvChieu.Rows[j + 1].Cells[i].Value.ToString());
                    li.Add(ti);
                }
            }
            return li;
        }

        public bool UpdateSang()
        {
            try
            {
                ConnectionController connection = new ConnectionController();
                string query = "UPDATE Schedule SET T2=@a1, T3=@a2, T4=@a3, T5=@a4, T6=@a5, T7=@a6 WHERE Class=@a7 and Session=@a8";
                MySqlCommand cmd = new MySqlCommand(query, connection.connection);

                List<time> liSang = GetListUpdateSang();
                cmd.Parameters.AddWithValue("@a1", liSang[0]._start + "-" + liSang[0]._end);
                cmd.Parameters.AddWithValue("@a2", liSang[1]._start + "-" + liSang[1]._end);
                cmd.Parameters.AddWithValue("@a3", liSang[2]._start + "-" + liSang[2]._end);
                cmd.Parameters.AddWithValue("@a4", liSang[3]._start + "-" + liSang[3]._end);
                cmd.Parameters.AddWithValue("@a5", liSang[4]._start + "-" + liSang[4]._end);
                cmd.Parameters.AddWithValue("@a6", liSang[5]._start + "-" + liSang[5]._end);
                cmd.Parameters.AddWithValue("@a7", cmbClass.Text);
                cmd.Parameters.AddWithValue("@a8", 1);
                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        public bool UpdateChieu()
        {
            try
            {
                ConnectionController connection = new ConnectionController();
                string query = "UPDATE Schedule SET T2=@a1, T3=@a2, T4=@a3, T5=@a4, T6=@a5, T7=@a6 WHERE Class=@a7 and Session=@a8";
                MySqlCommand cmd = new MySqlCommand(query, connection.connection);

                List<time> liChieu= GetListUpdateChieu();
                cmd.Parameters.AddWithValue("@a1", liChieu[0]._start + "-" + liChieu[0]._end);
                cmd.Parameters.AddWithValue("@a2", liChieu[1]._start + "-" + liChieu[1]._end);
                cmd.Parameters.AddWithValue("@a3", liChieu[2]._start + "-" + liChieu[2]._end);
                cmd.Parameters.AddWithValue("@a4", liChieu[3]._start + "-" + liChieu[3]._end);
                cmd.Parameters.AddWithValue("@a5", liChieu[4]._start + "-" + liChieu[4]._end);
                cmd.Parameters.AddWithValue("@a6", liChieu[5]._start + "-" + liChieu[5]._end);
                cmd.Parameters.AddWithValue("@a7", cmbClass.Text);
                cmd.Parameters.AddWithValue("@a8", 2);
                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        public void UpdateStudent(string className, string timeSang, string timeChieu)
        {
            try
            {
                ConnectionController connection = new ConnectionController();
                string query = "UPDATE studentinfo SET LateTime=@a1, Time2=@a2  WHERE Class=@a3 ";
                MySqlCommand cmd = new MySqlCommand(query, connection.connection);

                if (timeSang == "NULL")
                {
                    cmd.Parameters.AddWithValue("@a1", null);
                }
                else
                    cmd.Parameters.AddWithValue("@a1", timeSang);
                if (timeChieu == "NULL")
                {
                    cmd.Parameters.AddWithValue("@a2", null);
                }
                else
                    cmd.Parameters.AddWithValue("@a2", timeChieu);
                cmd.Parameters.AddWithValue("@a3", className);
                
                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    
                }                
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            DialogResult dlg = MessageBoxEx.Show("Do you want to update Schedule : " + cmbClass.Text,"Information ",MessageBoxButtons.YesNo,MessageBoxIcon.Information);
            if (dlg == DialogResult.Yes)
            {
                if (dgvSang.Rows.Count < 1)
                {
                    MessageBoxEx.Show("Morning Schedule is empty", "Warning ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                // Update 2 record cua class do 
                // 
                UpdateSang();
               
                if (dgvSang.Columns.Count > 0)
                {
                    int widthC = (dgvSang.Size.Width - 50)/ dgvSang.Columns.Count;
                    int i;
                    for ( i = 0; i < dgvSang.Columns.Count -1 ; i++)
                    {
                        dgvSang.Columns[i].Width = widthC ;
                    }
                    dgvSang.Columns[i].Width = widthC + 9;
                }

                if (dgvChieu.Rows.Count < 1)
                {
                    MessageBoxEx.Show("Afternoon Schedule is empty", "Warning ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                UpdateChieu();

                if (dgvChieu.Columns.Count > 0)
                {
                    int widthC = ( dgvChieu.Size.Width - 50)/ dgvChieu.Columns.Count ;
                    int i;
                    for ( i = 0; i < dgvChieu.Columns.Count -1 ; i++)
                    {
                        dgvChieu.Columns[i].Width = widthC ;
                    }
                    dgvChieu.Columns[i].Width = widthC + 9;
                }

                string lateTime="";
                if (dgvSang.Rows[0].Cells[0].Value.ToString() == "NONE")
                {
                    lateTime = "NULL";
                }
                else
                    lateTime = dgvSang.Rows[0].Cells[0].Value.ToString();

                
                string time2 = "";
                if (dgvChieu.Rows[0].Cells[0].Value.ToString() == "NONE")
                {
                    time2 = "NULL";
                }
                else
                    time2 = dgvChieu.Rows[0].Cells[0].Value.ToString();
                string className = cmbClass.Text;

                UpdateStudent(className, lateTime, time2);
            }          
        }

        private void Schedule_FormClosed(object sender, FormClosedEventArgs e)
        {
            Environment.Exit(Environment.ExitCode);
            Application.ExitThread();
            Application.Exit();
        }

        private void Schedule_FormClosing(object sender, FormClosingEventArgs e)
        {
            Environment.Exit(Environment.ExitCode);
            Application.ExitThread();
            Application.Exit();
        }

        private void Schedule_Resize(object sender, EventArgs e)
        {
            if (dgvSang.Columns.Count > 0)
            {
                int widthC = (dgvSang.Size.Width - 50)/ dgvSang.Columns.Count;
                int i;
                for ( i = 0; i < dgvSang.Columns.Count-1; i++)
                {
                    dgvSang.Columns[i].Width = widthC;
                }
                dgvSang.Columns[i].Width = widthC + 9;
            }

            if (dgvChieu.Columns.Count > 0)
            {
                int widthC = (dgvChieu.Size.Width - 50)/ dgvChieu.Columns.Count ;
                int i;
                for ( i = 0; i < dgvChieu.Columns.Count -1 ; i++)
                {
                    dgvChieu.Columns[i].Width = widthC;
                }
                dgvChieu.Columns[i].Width = widthC + 9;
            }
        }

                  
    }
}
